原文：[MySQL] 資料表結構遷移的好幫手 pt-online-schema-change

這邊跟大家介紹一套做 DB migration 的好東西 pt-online-schema-change，這是可以讓 DBA 省時省心力的工具，他可以用簡單的指令讓修改資料表結構時不鎖表(可寫入)與接近零 downtime 的好物。

在開發一套應用程式時，常常會根據不同的需求對資料庫結構進行變更，對 MySQL 而言更是令人頭痛，因為 MySQL 在修改資料表結構時會將資料表鎖住，這會讓伺服器無法正常服務進而造成使用者體驗不佳，但如果你的資料表又剛好是大型資料表更會讓鎖定時間拉長，所以一般的應用程式甚至是銀行業都會選擇在半夜進行修改，盡可能地讓 downtime 時間避開尖峰時段。

因為先前我都是使用 SQL Server 所以沒有遇過這個問題，為了測試我還請朋友幫我在接近 1T 的資料表中加個允許 Null 的欄位，輕輕鬆鬆一瞬間就做完了也沒有 Lock 過長的問題，難道這就是 M$ 有收保護費的成效？雖然 MySQL 5.6 開始為了避免鎖表，加強了這方面的功能讓鎖表變成可讀不可寫，但這使用體驗還是很差啊...

不過既然決議要用 MySQL 了那也沒辦法，我在找減少停機時間的解決方案時碰巧看到這款 tool，他可以用簡單的指令讓修改資料表結構時不鎖表(可寫入)與接近零 downtime ... 這不就是我需要的嗎 ！

pt-online-schema-change 的運作流程如下：

1. 檢查外來鍵是否存在，做相應設置的處理。
2. 新增一張新的資料表，其結構與來源資料表完全相同
   命名規則：_{source_table_name}_new
3. 根據 ALTER 語法修改新資料表結構
4. 在舊資料表上新增 INSERT, UPDATE, DELETE 觸發器，其目的為同步新資料至新資料表
5. 將舊資料表的資料複製至新資料表
6. 將新資料表加入外來鍵相關設定
7. 將兩張資料表的名字做交換，並將原舊資料表名稱為 old 的後綴，修改方式為原子操作(Atomic)以減少半殘狀態。
   命名規則：{source_table_name}_old

RENAME TABLE `exfast`.`tb_test` TO `exfast`.`_tb_test_old`, `exfast`.`_tb_test_new` TO `exfast`.`tb_test`

為了試驗這邊我準備了一張資料表其結構如下：

接著我要在這個資料表新增一個 age 的欄位，其指令為：

pt-online-schema-change \
--execute \
--progress=time,5 \
--print \
--alter-foreign-keys-method=rebuild_constraints \
--alter="ADD COLUMN age TINYINT" \
D=exfast,t=tb_test,h=172.17.0.2,P=3306,u=root,p=pass

由此可見這麼一小段簡單的指令這個 tool 就幫我們完成了麻煩的 7 個步驟實在是太佛心了吧！

這邊特別註解 --alter-foreign-keys-method 這個參數，這個參數共有三個值可以設定

1. rebuild_constraints
   會先將舊 table 的外來鍵 Constraints 刪除再添加至新表中，如果資料表很大的話會造成長時間阻塞，但也是最安全的方法。
2. drop_swap
   執行 FOREIGN_KEY_CHECKS = 0 禁止外來鍵 Constraints，先刪除原資料表再重命名新資料表。這種方式很快但是有風險：
   2.1. 在刪除原資料表和重命名新資料表的短時間內，資料表是不存在的程式會拋錯誤。
   2.2. 如果在重新命名的步驟出現錯誤無法復原。因為原表已經被刪除。
3. none
   類似 drop_swap 的處理方式，但是它不刪除原資料表，並且外來鍵 Constraints 會通過重新命名轉到老資料表上面。

參考資料：

1. online-schema-change
2. pt-online-schema-change 工具不锁表在线修改 MySQL 表结构